#![allow(dead_code)]
use sea_orm::{ActiveModelTrait, QueryOrder, ConnectionTrait, DatabaseConnection, EntityTrait, Order, ActiveValue::Set, Value};
use tracing::{debug, info, instrument};
use crate::entity::example_table;
use crate::entity::example_table::{ExampleTableResult};
use crate::utils::db_util::{ConditionOp, DBUtil, get_global_db_util};
// 测试入口函数
pub async fn run_db_util_tests() -> Result<(), Box<dyn std::error::Error>> {
    let db_util = get_global_db_util();
    info!("已初始化全局数据库连接池");

    // 测试插入操作
    test_insert(&db_util).await?;
    info!("插入测试完成");

    // 测试查询操作
    test_query(&db_util).await?;
    info!("查询测试完成");

    // 测试更新操作
    test_update(&db_util).await?;
    info!("更新测试完成");

    // 测试删除操作
    test_delete(&db_util).await?;
    info!("删除测试完成");

    // 测试事务操作
    test_transaction(&db_util).await?;
    info!("事务测试完成");

    // 测试自定义SQL查询
    test_raw_query(&db_util).await?;
    info!("自定义SQL查询测试完成");

    // 测试分页查询
    test_pagination(&db_util).await?;
    info!("分页查询测试完成");

    // 测试类型安全的SQL查询
    test_query_as(&db_util).await?;
    info!("类型安全的SQL查询测试完成");

    info!("所有 DBUtil 测试完成!");
    Ok(())
}

// pub async fn modify_shelf_monitor(data: ShelfMonitorReq) -> AjaxResult<()> {
//     let update_params = vec![
//         Value::Int(data.off_on),
//         opt_str_to_value(data.zh),
//     ];
//     let sql = "update account_info set off_on= ? where zh= ?";
//     let result=get_global_db_util().execute(sql, update_params).await;
//     match result {
//         Ok(_) => {
//             AjaxResult::succeed_without_data()
//         }
//         Err(_) => {
//             AjaxResult::internal_error("修改失败")
//         }
//     }
// }

// 创建测试表
#[allow(dead_code)]
async fn create_test_table(db: &DatabaseConnection) -> Result<(), Box<dyn std::error::Error>> {
    let create_table_sql = r#"
        CREATE TABLE IF NOT EXISTS example_table (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    "#;

    db.execute(sea_orm::Statement::from_string(
        sea_orm::DbBackend::Sqlite,
        create_table_sql.to_string(),
    ))
        .await?;

    Ok(())
}

// 测试插入操作
#[instrument(name = "test_insert", skip(db_util))]
async fn test_insert(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试插入操作...");

    // 创建测试记录 - 使用 Model
    let record = example_table::ActiveModel {
        name: Set("Test Item".to_string()),
        ..Default::default()
    };

    // 插入记录
    let inserted_record = db_util.insert(record).await?;
    debug!("插入的记录: {:?}", inserted_record);

    // 验证插入
    let fetched_record = example_table::Entity::find_by_id(inserted_record.id)
        .one(db_util.get_connection())
        .await?;

    assert!(fetched_record.is_some(), "插入的记录未找到");
    let fetched = fetched_record.unwrap();
    assert_eq!(fetched.name, "Test Item");
    debug!("验证插入成功");

    Ok(())
}

// 测试查询操作
#[instrument(name = "test_query", skip(db_util))]
async fn test_query(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试查询操作...");

    // 准备测试数据
    create_test_records(db_util).await?;

    // 使用查询构建器查询单个记录
    let record = db_util
        .query::<example_table::Entity>()
        .filter(example_table::Column::Name, ConditionOp::Eq,Value::String(Some(Box::new("Record 2".to_string()))))
        .one(db_util.get_connection())
        .await?;

    assert!(record.is_some(), "未找到测试记录");
    let record = record.unwrap();
    assert_eq!(record.name, "Record 2");
    debug!("找到记录: {:?}", record);

    // 查询所有记录
    let records = example_table::Entity::find()
        .order_by_asc(example_table::Column::Id)
        .all(db_util.get_connection())
        .await?;

    assert_eq!(records.len(), 3, "记录数量不正确");
    debug!("找到 {} 个记录", records.len());

    // 测试计数
    let count = db_util
        .query::<example_table::Entity>()
        .count(db_util.get_connection())
        .await?;

    assert_eq!(count, 3, "记录计数不正确");
    debug!("记录计数: {}", count);

    Ok(())
}

// 测试更新操作
#[instrument(name = "test_update", skip(db_util))]
async fn test_update(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试更新操作...");

    // 准备测试数据
    let record = create_test_record(db_util).await?;

    // 更新记录 - 转换为 Model
    let mut active_record: example_table::ActiveModel = record.clone().into();
    active_record.name = Set("Updated Record".to_string());

    let updated_record = db_util.update(active_record).await?;
    debug!("更新后的记录: {:?}", updated_record);

    // 验证更新
    let fetched_record = example_table::Entity::find_by_id(updated_record.id)
        .one(db_util.get_connection())
        .await?;

    assert!(fetched_record.is_some(), "更新后的记录未找到");
    let fetched = fetched_record.unwrap();
    assert_eq!(fetched.name, "Updated Record", "记录名称未更新");
    debug!("验证更新成功");

    Ok(())
}

// 测试删除操作
#[instrument(name = "test_delete", skip(db_util))]
async fn test_delete(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试删除操作...");

    // 准备测试数据
    let record = create_test_record(db_util).await?;

    // 删除记录
    db_util.delete::<example_table::Entity>(record.id).await?;
    debug!("记录已删除");

    // 验证删除
    let fetched_record = example_table::Entity::find_by_id(record.id)
        .one(db_util.get_connection())
        .await?;

    assert!(fetched_record.is_none(), "删除的记录仍存在");
    debug!("验证删除成功");

    Ok(())
}

// 测试事务操作
#[instrument(name = "test_transaction", skip(db_util))]
async fn test_transaction(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试事务操作...");

    // 开始事务
    let txn = db_util.begin_transaction().await?;
    debug!("事务已开始");

    // 在事务中插入数据
    let transaction_record = example_table::ActiveModel {
        name: Set("Transaction Record".to_string()),
        ..Default::default()
    };

    // 使用事务连接执行操作
    let inserted_record = transaction_record.insert(&txn).await?;
    debug!("在事务中插入的记录: {:?}", inserted_record);

    // 提交事务
    txn.commit().await?;
    debug!("事务已提交");

    // 验证插入
    let fetched_record = example_table::Entity::find_by_id(inserted_record.id)
        .one(db_util.get_connection())
        .await?;

    assert!(fetched_record.is_some(), "事务提交后记录未找到");
    debug!("验证事务提交成功");

    // 测试回滚
    let txn = db_util.begin_transaction().await?;
    debug!("新事务已开始");

    let record = example_table::ActiveModel {
        name: Set("Rollback Record".to_string()),
        ..Default::default()
    };

    let inserted_record2 = record.insert(&txn).await?;
    debug!("在事务中插入的记录: {:?}", inserted_record2);

    // 回滚事务
    txn.rollback().await?;
    debug!("事务已回滚");

    // 验证回滚
    let fetched_record2 = example_table::Entity::find_by_id(inserted_record2.id)
        .one(db_util.get_connection())
        .await?;

    assert!(fetched_record2.is_none(), "回滚的记录仍存在");
    debug!("验证事务回滚成功");

    Ok(())
}

// 测试自定义SQL查询
#[instrument(name = "test_raw_query", skip(db_util))]
async fn test_raw_query(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试自定义SQL查询...");

    // 准备测试数据
    create_test_records(db_util).await?;

    // 执行自定义SQL查询
    let sql = "SELECT * FROM example_table WHERE name LIKE ?";
    let params = vec![Value::String(Some(Box::from("%Record%".to_string())))];
    let results = db_util.raw_query(sql, params).await?;

    debug!("自定义SQL查询结果: {:?}", results);
    assert_eq!(results.len(), 3, "自定义SQL查询结果为空");

    //let first_record_id=results[0]["id"].as_i64().unwrap() as i32;
    let first_record_id = results[0]["id"].as_i64().unwrap() as i32;

    // 测试执行SQL语句
    let update_sql = "UPDATE example_table SET name = ? WHERE id = ?";
    let update_params = vec![
        Value::String(Some(Box::from("Updated via SQL".to_string()))),
        Value::Int(Some(first_record_id)),
    ];
    let rows_affected = db_util.execute(update_sql, update_params).await?;

    debug!("更新影响的行数: {}", rows_affected);
    assert_eq!(rows_affected, 1, "更新的行数不正确");

    // 验证更新结果
    let updated = example_table::Entity::find_by_id(first_record_id)
        .one(db_util.get_connection())
        .await?
        .unwrap();
    assert_eq!(updated.name, "Updated via SQL");

    Ok(())
}

// 测试分页查询
#[instrument(name = "test_pagination", skip(db_util))]
async fn test_pagination(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试分页查询...");

    // 准备测试数据
    create_test_records(db_util).await?;

    // 测试 Entity 分页
    let paginated_result = db_util
        .query::<example_table::Entity>()
        .order_by(example_table::Column::Id, Order::Asc)
        .paginate(db_util.get_connection(), 1, 2) // 第1页，每页2条
        .await?;

    debug!("分页查询结果: {:?}", paginated_result);
    assert_eq!(paginated_result.items.len(), 2, "分页项数量不正确");
    assert_eq!(paginated_result.total, 3, "分页总数不正确");
    assert_eq!(paginated_result.total_pages, 2, "分页总页数不正确");

    // 测试自定义SQL分页
    let data_sql = "SELECT * FROM example_table ORDER BY id LIMIT ? OFFSET ?";
    let count_sql = "SELECT COUNT(*) as count FROM example_table";
    let params = Vec::new();

    let sql_paginated_result = db_util
        .paginate_with_sql(data_sql, count_sql, params, 2, 1) // 第2页，每页1条
        .await?;

    debug!("自定义SQL分页查询结果: {:?}", sql_paginated_result);
    assert_eq!(sql_paginated_result.items.len(), 1, "SQL分页项数量不正确");
    assert_eq!(sql_paginated_result.total, 3, "SQL分页总数不正确");
    assert_eq!(sql_paginated_result.total_pages, 3, "SQL分页总页数不正确");

    // 测试简化版SQL分页
    let simple_sql_paginated_result = db_util
        .simple_paginate_with_sql(
            "SELECT * FROM example_table ORDER BY id",
            Vec::new(),
            1, // 第1页
            2  // 每页2条
        )
        .await?;

    debug!("简化版SQL分页查询结果: {:?}", simple_sql_paginated_result);
    assert_eq!(
        simple_sql_paginated_result.items.len(),
        2,
        "简化版SQL分页项数量不正确"
    );
    assert_eq!(
        simple_sql_paginated_result.total,
        3,
        "简化版SQL分页总数不正确"
    );
    assert_eq!(
        simple_sql_paginated_result.total_pages,
        2,
        "简化版SQL分页总页数不正确"
    );

    Ok(())
}

// 测试类型安全的SQL查询
#[instrument(name = "test_query_as", skip(db_util))]
async fn test_query_as(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    debug!("开始测试类型安全的SQL查询...");

    // 准备测试数据
    create_test_records(db_util).await?;

    // 执行类型安全的SQL查询
    let sql = "SELECT id, name FROM example_table WHERE id > ?";
    let params = vec![Value::Int(Some(0))];

    let records: Vec<ExampleTableResult> = db_util.query_as(sql, params).await?;

    debug!("类型安全的SQL查询结果: {:?}", records);
    assert_eq!(records.len(), 3, "类型安全的SQL查询结果为空");
    assert_eq!(records[0].name, "Record 1");

    Ok(())
}

// 创建单个测试记录
async fn create_test_record(db_util: &DBUtil) -> Result<example_table::Model, Box<dyn std::error::Error>> {
    let record = example_table::ActiveModel {
        name: Set("Temp Record".to_string()),
        ..Default::default()
    };

    let inserted_record = db_util.insert(record).await?;
    Ok(inserted_record)
}

// 创建多个测试记录
async fn create_test_records(db_util: &DBUtil) -> Result<(), Box<dyn std::error::Error>> {
    // 先清空测试表
    db_util.execute("DELETE FROM example_table", Vec::new()).await?;

    // 创建测试记录 - 使用 Model
    let records = vec![
        example_table::ActiveModel {
            name: Set("Record 1".to_string()),
            ..Default::default()
        },
        example_table::ActiveModel {
            name: Set("Record 2".to_string()),
            ..Default::default()
        },
        example_table::ActiveModel {
            name: Set("Record 3".to_string()),
            ..Default::default()
        },
    ];

    for record in records {
        db_util.insert(record).await?;
    }

    Ok(())
}